package com.central.apps.controller;

import com.alibaba.fastjson.JSONObject;
import com.central.app.common.util.ExcelUtil;
import com.central.app.service.HandlerService;
import com.central.apps.common.constant.InventoryConstant;
import org.apache.commons.collections.CollectionUtils;
import org.apache.commons.collections.MapUtils;
import org.apache.commons.lang3.StringUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.PostMapping;
import org.springframework.web.bind.annotation.RestController;
import org.springframework.web.multipart.MultipartFile;

import java.text.DateFormat;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.*;

/**
 * 自定义导入接口类
 *
 * @author 李大超
 * @date 2022/10/15
 */
@RestController
public class CustomImportController {
    @Autowired
    private HandlerService handlerService;

    @PostMapping("/importItemType")
    public String importItemType(MultipartFile file) throws Exception {
        JSONObject json = new JSONObject();
        json.put("code", "0");

        // 1.判断文件信息
        String result = ExcelUtil.checkFileMsg(file);
        if(StringUtils.isNotEmpty(result)){
            json.put("msg", result);
            return json.toString();
        }

        // 2.解析文件流获取excel明细信息
        HashMap<String, ArrayList<String[]>> hashMap = ExcelUtil.analysisFile(file);
        ArrayList<String[]> arrayList = hashMap.get("OK");
        if(CollectionUtils.isEmpty(arrayList)){
            Set<String> strings = hashMap.keySet();
            String next = strings.iterator().next();
            json.put("msg", next);
            return json.toString();
        }

        // 3.校验数据合理性
        List<String> failList = new ArrayList<>();
        for (int i = 0; i < arrayList.size(); i++) {
            String[] str = arrayList.get(i);

            String maxnum = "";
            String maxdesc = "";
            String mediumnum = "";
            String mediumdesc = "";
            String smallnum = "";
            String smalldesc = "";
            for (int j = 0; j < str.length; j++) {
                switch (j) {
                    case 0:
                        maxnum = str[j];
                        break;
                    case 1:
                        maxdesc = str[j];
                        break;
                    case 2:
                        mediumnum = str[j];
                        break;
                    case 3:
                        mediumdesc = str[j];
                        break;
                    case 4:
                        smallnum = str[j];
                        break;
                    case 5:
                        smalldesc = str[j];
                        break;
                    default:break;
                }
            }

            StringBuilder sbStr = new StringBuilder();
            boolean isException = false;
            if (StringUtils.isEmpty(maxnum) || StringUtils.isEmpty(maxdesc)) {
                isException = true;
            } else if ((StringUtils.isEmpty(mediumnum) || StringUtils.isEmpty(mediumdesc))
                    && ((StringUtils.isNotEmpty(smallnum) || StringUtils.isNotEmpty(smalldesc)))) {
                isException = true;
            }

            boolean isLenException = false;
            if ((StringUtils.isEmpty(maxnum) ? 1 : maxnum.length()) > 1
                    || ((StringUtils.isEmpty(mediumnum) ? 2 : mediumnum.length()) != 2)
                    || ((StringUtils.isEmpty(smallnum) ? 2 : smallnum.length()) != 2)) {
                isLenException = true;
            }

            if (isException) {
                sbStr.append("第");
                sbStr.append(i + 2);
                sbStr.append("行存在必填项未填; ");
                failList.add(sbStr.toString());
            } else if (isLenException) {
                sbStr.append("第");
                sbStr.append(i + 2);
                sbStr.append("行物料相关编码长度异常; ");
                failList.add(sbStr.toString());
            } else {
                // 判断物料大类是否存在
                boolean exists1 = false;
                StringBuilder searchSql = new StringBuilder();
                searchSql.append("select maxnum from gq_itemtype where maxnum = '" + maxnum + "'");
                List<Map<String, Object>> resultList1 = handlerService.findBySql(searchSql.toString());
                if (CollectionUtils.isNotEmpty(resultList1)) {
                    exists1 = true;
                }

                // 查询是否存在资产信息
                boolean exists2 = false;
                searchSql = new StringBuilder();
                searchSql.append("select mediumnum from gq_medium where maxnum = '" + maxnum + "' and mediumnum = '" + mediumnum + "'");
                List<Map<String, Object>> resultList2 = handlerService.findBySql(searchSql.toString());
                if (CollectionUtils.isNotEmpty(resultList2)) {
                    exists2 = true;
                }

                // 判断资产负责人是否存在
                boolean exists3 = false;
                searchSql = new StringBuilder();
                searchSql.append("select smallnum from gq_small where maxnum = '" + maxnum + "' and mediumnum = '" + mediumnum + "' and smallnum = '" + smallnum + "'");

                List<Map<String, Object>> resultList3 = handlerService.findBySql(searchSql.toString());
                if (CollectionUtils.isNotEmpty(resultList3)) {
                    exists3 = true;
                }

                if (exists1 && exists2 && exists3) {
                    sbStr.append("第");
                    sbStr.append(i + 2);
                    sbStr.append("行");
                    sbStr.append("物料类型信息均已存在; ");
                    failList.add(sbStr.toString());
                }
            }
        }

        if (CollectionUtils.isNotEmpty(failList)) {
            json.put("msg", "导入数据存在问题, 请仔细阅读提示信息：" + failList.toString());
            return json.toString();
        }

        // 4.组织导入数据并导入
        List<Map<String, Object>> maxDatas = new ArrayList<>();
        List<Map<String, Object>> mediumDatas = new ArrayList<>();
        List<Map<String, Object>> smallDatas = new ArrayList<>();
        for (int i = 0; i < arrayList.size(); i++) {
            String[] str = arrayList.get(i);

            String maxnum = "";
            String maxdesc = "";
            String mediumnum = "";
            String mediumdesc = "";
            String smallnum = "";
            String smalldesc = "";
            for (int j = 0; j < str.length; j++) {
                switch (j) {
                    case 0:
                        maxnum = str[j];
                        break;
                    case 1:
                        maxdesc = str[j];
                        break;
                    case 2:
                        mediumnum = str[j];
                        break;
                    case 3:
                        mediumdesc = str[j];
                        break;
                    case 4:
                        smallnum = str[j];
                        break;
                    case 5:
                        smalldesc = str[j];
                        break;
                    default:break;
                }
            }

            // 组织插入信息
            Map<String, Object> maxMap = new HashMap<>(2);
            maxMap.put("maxnum", maxnum);
            maxMap.put("maxdesc", maxdesc);

            Map<String, Object> mediunmMap = new HashMap<>(3);
            mediunmMap.put("maxnum", maxnum);
            mediunmMap.put("mediumnum", mediumnum);
            mediunmMap.put("mediumdesc", mediumdesc);

            Map<String, Object> smallMap = new HashMap<>(5);
            smallMap.put("maxnum", maxnum);
            smallMap.put("mediumnum", mediumnum);
            smallMap.put("mediumdesc", mediumdesc);
            smallMap.put("smallnum", smallnum);
            smallMap.put("smalldesc", smalldesc);

            if (!maxDatas.contains(maxMap)) {
                maxDatas.add(maxMap);
            }
            if (!mediumDatas.contains(mediunmMap)) {
                mediumDatas.add(mediunmMap);
            }
            if (!smallDatas.contains(smallMap)) {
                smallDatas.add(smallMap);
            }
        }

        if (CollectionUtils.isNotEmpty(maxDatas)) {
            handlerService.batchInsert("itemtypeApp", "gq_itemtype", maxDatas);
        }
        if (CollectionUtils.isNotEmpty(mediumDatas)) {
            handlerService.batchInsert("itemtypeApp", "gq_medium", mediumDatas);
        }
        if (CollectionUtils.isNotEmpty(smallDatas)) {
            handlerService.batchInsert("itemtypeApp", "gq_small", smallDatas);
        }

        json.put("msg", "导入成功");
        return json.toString();
    }

    @PostMapping("/importItem")
    public String importItem(MultipartFile file) throws Exception {
        JSONObject json = new JSONObject();
        json.put("code", "0");

        // 1.判断文件信息
        String result = ExcelUtil.checkFileMsg(file);
        if(StringUtils.isNotEmpty(result)){
            json.put("msg", result);
            return json.toString();
        }

        // 2.解析文件流获取excel明细信息
        HashMap<String, ArrayList<String[]>> hashMap = ExcelUtil.analysisFile(file);
        ArrayList<String[]> arrayList = hashMap.get("OK");
        if(CollectionUtils.isEmpty(arrayList)){
            Set<String> strings = hashMap.keySet();
            String next = strings.iterator().next();
            json.put("msg", next);
            return json.toString();
        }

        // 3.校验数据合理性
        List<String> failList = new ArrayList<>();
        for (int i = 0; i < arrayList.size(); i++) {
            String[] str = arrayList.get(i);

            String maxnum = "";// 物料大类编码
            String mediumnum = "";// 物料中类编码
            String smallnum = "";// 物料小类编码
            String description = "";// 物料名称
            for (int j = 0; j < str.length; j++) {
                switch (j) {
                    case 0:
                        maxnum = str[j];
                        break;
                    case 1:
                        mediumnum = str[j];
                        break;
                    case 2:
                        smallnum = str[j];
                        break;
                    case 4:
                        description = str[j];
                        break;
                    default:
                        break;
                }
            }
            StringBuilder itemnum = new StringBuilder();
            itemnum.append(maxnum);
            itemnum.append(mediumnum);
            itemnum.append(smallnum);

            StringBuilder sbStr = new StringBuilder();
            boolean isException = false;
            if (StringUtils.isEmpty(maxnum)) {
                isException = true;
            } else if (StringUtils.isEmpty(mediumnum) && StringUtils.isNotEmpty(smallnum)) {
                isException = true;
            }
            if (StringUtils.isEmpty(description)) {
                isException = true;
            }

            boolean isLenException = false;
            if ((StringUtils.isEmpty(maxnum) ? 1 : maxnum.length()) > 1
                    || ((StringUtils.isEmpty(mediumnum) ? 2 : mediumnum.length()) != 2)
                    || ((StringUtils.isEmpty(smallnum) ? 2 : smallnum.length()) != 2)) {
                isLenException = true;
            }

            if (isException) {
                sbStr.append("第");
                sbStr.append(i + 2);
                sbStr.append("行存在必填项未填; ");
                failList.add(sbStr.toString());
            } else if (isLenException) {
                sbStr.append("第");
                sbStr.append(i + 2);
                sbStr.append("行物料相关编码长度异常; ");
                failList.add(sbStr.toString());
            }  else {
                // 判断物料大类是否存在
                boolean exists = false;
                StringBuilder searchSql = new StringBuilder();
                searchSql.append("select itemnum from gq_item where itemnum = '" + itemnum + "'");
                List<Map<String, Object>> resultList1 = handlerService.findBySql(searchSql.toString());
                if (CollectionUtils.isNotEmpty(resultList1)) {
                    exists = true;
                }

                if (exists) {
                    sbStr.append("第");
                    sbStr.append(i + 2);
                    sbStr.append("行");
                    sbStr.append("物料信息均已存在; ");
                    failList.add(sbStr.toString());
                }
            }
        }

        if (CollectionUtils.isNotEmpty(failList)) {
            json.put("msg", "导入数据存在问题, 请仔细阅读提示信息：" + failList.toString());
            return json.toString();
        }

        // 4.组织导入数据并导入
        List<Map<String, Object>> itemDatas = new ArrayList<>();
        for (int i = 0; i < arrayList.size(); i++) {
            String[] str = arrayList.get(i);
            // 组织插入信息
            Map<String, Object> itemMap = new HashMap<>();
            StringBuilder itemnum = new StringBuilder();
            itemnum.append(str[0]);
            itemnum.append(str[1]);
            itemnum.append(str[2]);

            // 查询物料编码最大流水号
            List<Map<String, Object>> itemList = handlerService.findBySql("select SUBSTR(itemnum,6,6) as idx from gq_item where itemnum like '" + itemnum + "%'");
            Integer itemnumIdx = InventoryConstant.ITEMNUM_INIT_IDX;
            if(CollectionUtils.isNotEmpty(itemList)){
                Map<String, Object> map = itemList.get(0);
                String idx = MapUtils.getString(map, "idx");
                if(StringUtils.isNotBlank(idx)){
                    itemnumIdx = Integer.parseInt(idx);
                }
            }
            itemnumIdx = itemnumIdx + 1;
            itemnum.append(itemnumIdx);

            itemMap.put("itemnum", itemnum.toString());
            itemMap.put("rawmatecode", str[3]);
            itemMap.put("description", str[4]);
            itemMap.put("model", str[5]);
            itemMap.put("unit", str[6]);
            itemMap.put("itemtype", str[7]);
            itemMap.put("lifemoney", str[8]);
            itemMap.put("packform", str[9]);
            itemMap.put("supplier", str[10]);
            itemMap.put("spemodel", str[11]);
            itemMap.put("maintechtors", str[12]);
            itemMap.put("qualitygrade", str[13]);
            itemMap.put("status", "自由");

            itemDatas.add(itemMap);
        }

        if (CollectionUtils.isNotEmpty(itemDatas)) {
            handlerService.batchInsert("itemApp", "gq_item", itemDatas);
        }

        json.put("msg", "导入成功");
        return json.toString();
    }

    @PostMapping("/importStore")
    public String importStore(MultipartFile file) throws Exception {
        JSONObject json = new JSONObject();
        json.put("code", "0");

        // 1.判断文件信息
        String result = ExcelUtil.checkFileMsg(file);
        if(StringUtils.isNotEmpty(result)){
            json.put("msg", result);
            return json.toString();
        }

        // 2.解析文件流获取excel明细信息
        HashMap<String, ArrayList<String[]>> hashMap = ExcelUtil.analysisFile(file);
        ArrayList<String[]> arrayList = hashMap.get("OK");
        if(CollectionUtils.isEmpty(arrayList)){
            Set<String> strings = hashMap.keySet();
            String next = strings.iterator().next();
            json.put("msg", next);
            return json.toString();
        }

        // 3.校验数据合理性
        List<String> failList = new ArrayList<>();
        for (int i = 0; i < arrayList.size(); i++) {
            String[] str = arrayList.get(i);

            String storenum = "";// 仓库编码
            String storedesc = "";// 仓库名称
            String shelfnum = "";// 货位编号
            String shelfdesc = "";// 货位名称
            String rownum = "";// 货架编号
            for (int j = 0; j < str.length; j++) {
                switch (j) {
                    case 0:
                        storenum = str[j];
                        break;
                    case 1:
                        storedesc = str[j];
                        break;
                    case 3:
                        shelfnum = str[j];
                        break;
                    case 4:
                        shelfdesc = str[j];
                        break;
                    case 5:
                        rownum = str[j];
                        break;
                    default:
                        break;
                }
            }

            StringBuilder sbStr = new StringBuilder();
            boolean isException = false;
            if (StringUtils.isEmpty(storenum) || StringUtils.isEmpty(storedesc)) {
                isException = true;
            } else if (StringUtils.isEmpty(shelfnum) && (StringUtils.isEmpty(shelfdesc) && StringUtils.isEmpty(rownum))) {

            } else if (StringUtils.isEmpty(shelfnum) || (StringUtils.isEmpty(shelfdesc) || StringUtils.isEmpty(rownum))) {
                isException = true;
            }

            if (isException) {
                sbStr.append("第");
                sbStr.append(i + 2);
                sbStr.append("行存在必填项未填; ");
                failList.add(sbStr.toString());
            }
        }

        if (CollectionUtils.isNotEmpty(failList)) {
            json.put("msg", "导入数据存在问题, 请仔细阅读提示信息：" + failList.toString());
            return json.toString();
        }

        // 4.组织导入数据并导入
        List<Map<String, Object>> storeDatas = new ArrayList<>();
        List<Map<String, Object>> areashelfDatas = new ArrayList<>();
        for (int i = 0; i < arrayList.size(); i++) {
            String[] str = arrayList.get(i);

            // 判断物料大类是否存在
            boolean exists = false;
            StringBuilder searchSql = new StringBuilder();
            searchSql.append("select storenum from gq_store where storenum = '" + str[0] + "'");
            List<Map<String, Object>> resultList1 = handlerService.findBySql(searchSql.toString());
            if (CollectionUtils.isNotEmpty(resultList1)) {
                exists = true;
            }

            if (!exists) {
                // 组织插入信息
                Map<String, Object> storeMap = new HashMap<>();

                storeMap.put("storenum", str[0]);
                storeMap.put("storedesc", str[1]);
                storeMap.put("waregrade", str[2]);
                storeMap.put("managernum", str[8]);
                storeMap.put("managerdesc", str[9]);
                storeMap.put("deptcode", str[10]);
                storeMap.put("status", "使用中");

                storeDatas.add(storeMap);
            }

            // 组织插入信息
            Map<String, Object> areashelfMap = new HashMap<>();
            areashelfMap.put("storenum", str[0]);
            areashelfMap.put("shelfnum", str[3]);
            areashelfMap.put("shelfdesc", str[4]);
            areashelfMap.put("rownum", str[5]);
            areashelfMap.put("storeynum", str[6]);
            areashelfMap.put("tagnum", str[7]);

            areashelfDatas.add(areashelfMap);
        }

        if (CollectionUtils.isNotEmpty(storeDatas)) {
            handlerService.batchInsert("storeApp", "gq_store", storeDatas);
        }
        if (CollectionUtils.isNotEmpty(areashelfDatas)) {
            handlerService.batchInsert("storeApp", "gq_areashelf", areashelfDatas);
        }

        json.put("msg", "导入成功");
        return json.toString();
    }

    @PostMapping("/importSupplier")
    public String importSupplier(MultipartFile file) throws Exception {
        JSONObject json = new JSONObject();
        json.put("code", "0");

        // 1.判断文件信息
        String result = ExcelUtil.checkFileMsg(file);
        if(StringUtils.isNotEmpty(result)){
            json.put("msg", result);
            return json.toString();
        }

        // 2.解析文件流获取excel明细信息
        HashMap<String, ArrayList<String[]>> hashMap = ExcelUtil.analysisFile(file);
        ArrayList<String[]> arrayList = hashMap.get("OK");
        if(CollectionUtils.isEmpty(arrayList)){
            Set<String> strings = hashMap.keySet();
            String next = strings.iterator().next();
            json.put("msg", next);
            return json.toString();
        }

        // 3.校验数据合理性
        List<String> failList = new ArrayList<>();
        for (int i = 0; i < arrayList.size(); i++) {
            String[] str = arrayList.get(i);

            String suppliernum = "";// 供应商编号
            String suppliername = "";// 供应商名称
            for (int j = 0; j < str.length; j++) {
                switch (j) {
                    case 0:
                        suppliernum = str[j];
                        break;
                    case 1:
                        suppliername = str[j];
                        break;
                    default:
                        break;
                }
            }

            StringBuilder sbStr = new StringBuilder();
            boolean isException = false;
            if (StringUtils.isEmpty(suppliernum) || StringUtils.isEmpty(suppliername)) {
                isException = true;
            }

            if (isException) {
                sbStr.append("第");
                sbStr.append(i + 2);
                sbStr.append("行存在必填项未填; ");
                failList.add(sbStr.toString());
            }

            // 判断供应商是否存在
            if (StringUtils.isNotEmpty(suppliernum) && StringUtils.isNotEmpty(suppliername)) {
                List<Map<String, Object>> resultList1 = handlerService.findBySql("select supplierno from gq_account where supplierno = '" + suppliernum + "'");
                if (CollectionUtils.isNotEmpty(resultList1)) {
                    sbStr.append("第");
                    sbStr.append(i + 2);
                    sbStr.append("行供应商已经存在; ");
                    failList.add(sbStr.toString());
                }
            }

            // 校验有效期
            boolean isDateException = false;
            int[] intArray = {10, 11, 12, 13, 30, 35, 40, 44, 47};
            for (int l = 0; l < intArray.length; l++) {
                if (StringUtils.isNotEmpty(str[intArray[l]])) {
                    DateFormat df = new SimpleDateFormat("yyyy/MM/dd");
                    try {
                        df.parse(str[intArray[l]]);
                    } catch (ParseException e1) {
                        try {
                            df = new SimpleDateFormat("yyyy-MM-dd");
                            df.parse(str[intArray[l]]);
                        } catch (ParseException e2) {
                            isDateException = true;
                        }
                    }
                }
            }

            if (isDateException) {
                sbStr.append("第");
                sbStr.append(i + 2);
                sbStr.append("行供应商相关有效期日期格式仅支持yyyy/MM/dd或者yyyy-MM-dd; ");
                failList.add(sbStr.toString());
            }

            boolean isNumException = false;
            if (StringUtils.isNotEmpty(str[26])) {
                try {
                    Double regiscapital = Double.parseDouble(str[26]);
                } catch (Exception e) {
                    isNumException = true;
                }
            }

            if (isNumException) {
                sbStr.append("第");
                sbStr.append(i + 2);
                sbStr.append("行供应商注册资金格式错误; ");
                failList.add(sbStr.toString());
            }
        }

        if (CollectionUtils.isNotEmpty(failList)) {
            json.put("msg", "导入数据存在问题, 请仔细阅读提示信息：" + failList.toString());
            return json.toString();
        }

        // 4.组织导入数据并导入
        List<Map<String, Object>> supplierDatas = new ArrayList<>();
        for (int i = 0; i < arrayList.size(); i++) {
            String[] str = arrayList.get(i);

            // 组织插入信息
            Map<String, Object> supplierMap = new HashMap<>();

            supplierMap.put("supplierno", str[0]);// 供应商编号
            supplierMap.put("suppliername", str[1]);// 供应商名称
            supplierMap.put("othername", str[2]);// 其它名称
            supplierMap.put("suppype", str[3]);// 供方类型
            supplierMap.put("classification", str[4]);// 1级分类
            supplierMap.put("leveification", str[5]);// 2级分类
            supplierMap.put("supgory", str[6]);// 供应商类别
            supplierMap.put("supliergrade", str[7]);// 供应商等级
            supplierMap.put("address", str[8]);// 地址
            supplierMap.put("qualification", str[9]);// 专业资质
            // 营业执照有效期
            if (StringUtils.isNotEmpty(str[10])) {
                supplierMap.put("business", str[10]);
            }
            // 专业资质有效期
            if (StringUtils.isNotEmpty(str[11])) {
                supplierMap.put("professional", str[11]);
            }
            // 安全资质有效期
            if (StringUtils.isNotEmpty(str[12])) {
                supplierMap.put("safetyq", str[12]);
            }
            // 黑名单有效期
            if (StringUtils.isNotEmpty(str[13])) {
                supplierMap.put("exblacklist", str[13]);
            }
            supplierMap.put("dismeasures", str[14]);// 处理措施
            supplierMap.put("Identificalicant", str[15]);// 认定申请单位
            supplierMap.put("Identificastage", str[16]);// 认定阶段
            supplierMap.put("tin", str[17]);// 纳税人识别号

            supplierMap.put("contacts", str[18]);// 联系人
            supplierMap.put("number", str[19]);// 联系电话
            supplierMap.put("Fax", str[20]);// 传真
            supplierMap.put("deposit", str[21]);// 开户银行
            supplierMap.put("account", str[22]);// 银行账号
            supplierMap.put("legal", str[23]);// 法定代表人
            supplierMap.put("nature", str[24]);// 企业性质
            supplierMap.put("license", str[25]);// 营业执照注册码
            supplierMap.put("regiscapital", str[26]);// 注册资金(万元)
            supplierMap.put("scopebuslicen", str[27]);// 营业执照许可范围
            supplierMap.put("confidenlevel", str[28]);// 保密等级
            supplierMap.put("confitiacertno", str[29]);// 保密证书编号
            // 保密证书有效期
            if (StringUtils.isNotEmpty(str[30])) {
                supplierMap.put("expdaconficate", str[30]);
            }
            supplierMap.put("Interlquatem", str[31]);// M1有无
            supplierMap.put("internaquatemtion", str[32]);// M1认证证书
            supplierMap.put("Internaquacerno", str[33]);// M1编号
            supplierMap.put("scopeinternaqualcer", str[34]);// M1认证范围
            // M1有效期
            if (StringUtils.isNotEmpty(str[35])) {
                supplierMap.put("exdateinterquacer", str[35]);
            }
            supplierMap.put("milistquatem", str[36]);// M2有无
            supplierMap.put("eaequasyscertiunit", str[37]);// M2认证单位
            supplierMap.put("weaeququalsyscernum", str[38]);// M2编号
            supplierMap.put("scoweaquasytification", str[39]);// M2认证范围

            // M2有效期
            if (StringUtils.isNotEmpty(str[40])) {
                supplierMap.put("exdateweacertificate", str[40]);
            }

            supplierMap.put("equmancation", str[41]);// M3有无
            supplierMap.put("eaequasyscertiunit", str[42]);// M3注册证书编号
            supplierMap.put("cerequmanregcertifi", str[43]);// M3认证范围
            // M3有效期至
            if (StringUtils.isNotEmpty(str[44])) {
                supplierMap.put("expdateregcerequman", str[44]);
            }

            supplierMap.put("sciretionlicen", str[45]);// M4有无
            supplierMap.put("scientresprodulic", str[46]);// M4编号
            // M4有效期至
            if (StringUtils.isNotEmpty(str[47])) {
                supplierMap.put("expirydasciresprolic", str[47]);
            }
            supplierMap.put("otherqualifica", str[48]);// 其他资质
            supplierMap.put("type", 1);// TODO

            supplierDatas.add(supplierMap);
        }

        if (CollectionUtils.isNotEmpty(supplierDatas)) {
            handlerService.batchInsert("accountApp", "gq_account", supplierDatas);
        }

        json.put("msg", "导入成功");
        return json.toString();
    }

    @PostMapping("/importDict")
    public String importDict(MultipartFile file) throws Exception {
        JSONObject json = new JSONObject();
        json.put("code", "0");

        // 1.判断文件信息
        String result = ExcelUtil.checkFileMsg(file);
        if(StringUtils.isNotEmpty(result)){
            json.put("msg", result);
            return json.toString();
        }

        // 2.解析文件流获取excel明细信息
        Map<String, ArrayList<String[]>> hashMap = ExcelUtil.analysisFileExtension(file);
        if (hashMap.isEmpty()) {
            json.put("msg", "Excel文件sheet为空!");
            return json.toString();
        }

        // 3.校验数据是否合格
        List<String> failList = new ArrayList<>();
        int index = 1;
        for (String key : hashMap.keySet()) {
            ArrayList<String[]> arrayList = hashMap.get(key);

            String[] str = arrayList.get(0);

            // 判断数据字典是否存在
            boolean exists = false;
            StringBuilder searchSql = new StringBuilder();
            searchSql.append("select id from sys_dict where name = '" + str[0] + "'");
            List<Map<String, Object>> resultList1 = handlerService.findBySql(searchSql.toString());
            if (CollectionUtils.isNotEmpty(resultList1)) {
                exists = true;
            }

            if (!exists) {
                String sbStr = "第" + index + "个sheet表数据字典不存在; ";
                failList.add(sbStr);
            }

            index++;
        }

        if (CollectionUtils.isNotEmpty(failList)) {
            json.put("msg", "导入数据存在问题, 请仔细阅读提示信息：" + failList.toString());
            return json.toString();
        }

        // 4.组织导入数据并导入
        for (String key : hashMap.keySet()) {
            ArrayList<String[]> arrayList = hashMap.get(key);

            // 处理数据字典抬头
            String dictParentName = arrayList.get(0)[0];
            // 查询数据字典主表信息
            List<Map<String, Object>> dictList = handlerService.findBySql("select id from sys_dict where name = '" + dictParentName + "'");
            Map<String, Object> dictMap = dictList.get(0);
            Integer dictId = MapUtils.getInteger(dictMap, "id");
            // 清理数据
            handlerService.updateBySql("delete from sys_dict where pid = " + dictId);

            for (int i = 1; i < arrayList.size(); i++) {
                String[] str = arrayList.get(i);

                handlerService.updateBySql("insert into sys_dict(id, pid, code, name, tips, sort) values (null, "+dictId+", '"+str[0]+"', '"+str[0]+"', '"+str[0]+"', "+ i +")");
            }
        }

        json.put("msg", "导入成功");
        return json.toString();
    }
}
